﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace AlphaSolutionsGP
{
    public class Qualification
    {
        #region properties         
            
            //qualification id
            private int id_Qualification;
            //qualification type
            private String type;
            //qualification grade
            private String grade;
            //qualification subject
            private String subject;
            //the year in which the qualification has been achieved
            private String date_achieved;
            //the qualification title
            private String title;
        #endregion

        //the defaulf constructor 
        #region constructors
        public Qualification()
        {
            type = "";
            title = "";
            subject = "";
            grade = "";
            date_achieved = "";     
        }

        //the constructor the the qualification class
        public Qualification(int qId, string TYPE, string TITLE, string SUBJECT, string GRADE, string DATE_ACHIEVED)
        {
            this.id_Qualification = qId;
            this.type = TYPE;
            this.title = TITLE;
            this.subject = SUBJECT;
            this.grade = GRADE;
            this.date_achieved = DATE_ACHIEVED;    
        }
        #endregion

        //mutators and accessors for each property 
        #region getters and setters       

        public String Type
        {
            get { return type; }
            set { type = value; }
        }

        public String Title
        {
            get { return title; }
            set { title = value; }
        }

        public String Grade
        {
            get { return grade; }
            set { grade = value; }
        }

        public String Subject
        {
            get { return subject; }
            set { subject = value; }
        }

        public String Date_Achieved
        {
            get { return date_achieved; }
            set { date_achieved = value; }
        }
        #endregion

        #region methods
        // change the old values to the new values amended into the form
        public void amendQualification()
        {                
             MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
             try
             {
                 connection.Open();
             }
             catch
             {
                 System.Windows.Forms.MessageBox.Show("Connection failed");
             }
             try
             {
                 MySqlCommand cmd = connection.CreateCommand();
                 cmd.CommandText = "UPDATE QUALIFICATION SET type='" + type + "', title='" + title + "', subject='" + subject + "', grade='" + grade + "', date_achieved='" + date_achieved + "' WHERE id_Qualification=" + id_Qualification;
                 MySqlDataReader run = cmd.ExecuteReader();
                 System.Windows.Forms.MessageBox.Show("The Qualification has been modified.", "Modify OK", System.Windows.Forms.MessageBoxButtons.OK);
             }
             catch (Exception ex)
             {
                 System.Windows.Forms.MessageBox.Show("Unable to modify due to the following error:\n\n" + ex.ToString());
             }
        }

        // create a new qualification
        public void createQualification(string sId)
        {
           MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Connection failed");
            }
            try
            {   
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "INSERT INTO QUALIFICATION (STUDENT_student_ID, type, title, subject, grade, date_achieved) VALUES ('"  + sId + "','" + type + "','" + title + "','" + subject + "','" + grade + "','" + date_achieved + "');";
                MySqlDataReader run = cmd.ExecuteReader();
                System.Windows.Forms.MessageBox.Show("The Qualification has been inserted.", "Insert OK", System.Windows.Forms.MessageBoxButtons.OK);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to insert due to the following error:\n\n" + ex.ToString());
            }
        }

        // remove an existing qualification from the DB
        public void removeQualification()
        {
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Connection failed");
            }
            try
            {
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "DELETE FROM QUALIFICATION WHERE id_Qualification=" + id_Qualification;
                MySqlDataReader run = cmd.ExecuteReader();
                System.Windows.Forms.MessageBox.Show("The Qualification has been deleted.", "Delete OK", System.Windows.Forms.MessageBoxButtons.OK);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to delete due to the following error:\n\n" + ex.ToString());
            }
        }

        //method to copy data from one qualification to another
        public void copyQualification(Qualification copy)
        {
            this.type = copy.type;
            this.title = copy.title;
            this.subject = copy.subject;
            this.grade = copy.grade;
            this.date_achieved = copy.date_achieved;
        }
        #endregion
    }
}
